/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package swa.e_store.businessLogic;

import java.util.Iterator;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import swa.e_store.DTO.ShopReportDTO;
import swa.e_store.entity.Purchase;

/**
 *
 * @author Stefan
 */
public class BL_Report {
    
    public BL_Report(){
        
    }
    
    public ShopReportDTO createMonthlyReports(){
        //EMFactory
        EntityManagerFactory emFactory = Persistence.createEntityManagerFactory("Napier_E-StorePU");

        //EntityManager
        EntityManager eManager = emFactory.createEntityManager();

        //Start Transaction
        EntityTransaction tx = eManager.getTransaction();
        tx.begin();
/**

select * from purchase where purchaseDate >= '2011-2-1' and purchaseDate <= '2011-2-28';
select price from purchase where purchaseDate >= '2011-3-1' and purchaseDate <= '2011-3-31'
select price from purchase where purchaseDate >= '2011-4-1' and purchaseDate <= '2011-4-30'
select price from purchase where purchaseDate >= '2011-5-1' and purchaseDate <= '2011-5-31'
select price from purchase where purchaseDate >= '2011-6-1' and purchaseDate <= '2011-6-30'
select price from purchase where purchaseDate >= '2011-7-1' and purchaseDate <= '2011-7-31'
select price from purchase where purchaseDate >= '2011-8-1' and purchaseDate <= '2011-8-31'
select price from purchase where purchaseDate >= '2011-9-1' and purchaseDate <= '2011-9-30'
select price from purchase where purchaseDate >= '2011-10-1' and purchaseDate <= '2011-10-31'
select price from purchase where purchaseDate >= '2011-11-1' and purchaseDate <= '2011-11-30'
select price from purchase where purchaseDate >= '2011-12-1' and purchaseDate <= '2011-12-31'
 * 
 */
        //Create Query
        Query query;
        
        //Janary
        query = eManager.createNativeQuery("select price from purchase where purchaseDate >= '2011-1-1' and purchaseDate <= '2011-1-31'");
        float jan = getMonthTotal(query);
        query = eManager.createNativeQuery("select price from purchase where purchaseDate >= '2011-2-1' and purchaseDate <= '2011-2-28'");
        float feb = getMonthTotal(query);
        query = eManager.createNativeQuery("select price from purchase where purchaseDate >= '2011-3-1' and purchaseDate <= '2011-3-31'");
        float mar = getMonthTotal(query);
        query = eManager.createNativeQuery("select price from purchase where purchaseDate >= '2011-4-1' and purchaseDate <= '2011-4-30'");
        float apr = getMonthTotal(query);
        query = eManager.createNativeQuery("select price from purchase where purchaseDate >= '2011-5-1' and purchaseDate <= '2011-5-31'");
        float mai = getMonthTotal(query);
        query = eManager.createNativeQuery("select price from purchase where purchaseDate >= '2011-6-1' and purchaseDate <= '2011-6-30'");
        float jun = getMonthTotal(query);
        query = eManager.createNativeQuery("select price from purchase where purchaseDate >= '2011-7-1' and purchaseDate <= '2011-7-31'");
        float jul = getMonthTotal(query);
        query = eManager.createNativeQuery("select price from purchase where purchaseDate >= '2011-8-1' and purchaseDate <= '2011-8-31'");
        float aug = getMonthTotal(query);
        query = eManager.createNativeQuery("select price from purchase where purchaseDate >= '2011-9-1' and purchaseDate <= '2011-9-30'");
        float sept = getMonthTotal(query);
        query = eManager.createNativeQuery("select price from purchase where purchaseDate >= '2011-10-1' and purchaseDate <= '2011-10-31'");
        float okt = getMonthTotal(query);
        query = eManager.createNativeQuery("select price from purchase where purchaseDate >= '2011-11-1' and purchaseDate <= '2011-11-30'");
        float nov = getMonthTotal(query);
        query = eManager.createNativeQuery("select price from purchase where purchaseDate >= '2011-12-1' and purchaseDate <= '2011-12-31'");
        float dez = getMonthTotal(query);
    
        
        //Closes
        eManager.close();
        emFactory.close();
        //System.out.println(jan + feb + mar+ apr+mai+jun+jul+aug+sept+okt+nov+dez);
        
        //Create Table
        String[] columnNames = {"Month", "Total Sales"};
        
        Object[][] data = {
            {"January", new Float(jan)},
            {"February", new Float(feb)},
            {"March", new Float(mar)},
            {"April", new Float(apr)},
            {"Mai", new Float(mai)},
            {"June", new Float(jun)},
            {"July", new Float(jul)},
            {"August", new Float(aug)},
            {"October", new Float(okt)},
            {"November", new Float(nov)},
            {"Dezember", new Float(dez)},
            
        };

        
        // In DTO Abfüllen
        ShopReportDTO reportDTO = new ShopReportDTO();
        reportDTO.setColumnNames(columnNames);
        reportDTO.setData(data);
        
        return reportDTO;




//        List stList = query.getResultList();
//        Iterator stIterator = stList.iterator();
//        while (stIterator.hasNext()) {
//            String price = stIterator.next().toString();
//            float lprice = (new Float(price).floatValue());
//            System.out.print("price:" + lprice);
//            System.out.println();
        
        //Purchase testPurch = (Purchase)resultList.get(0);
        
//        List<Purchase> purchaseList = query.getResultList();
//        long totalJanuary = 0l;
//        
//        Iterator<Purchase> it = purchaseList.iterator();
//        while (it.hasNext()) {
//            totalJanuary += it.next().getPrice();
//        }       
//        
//        System.out.println("Total January: "+ totalJanuary);

    }
    
    private float getMonthTotal(Query query){
        float monthTotal = 0f;
        List januaryAmountList = query.getResultList();
        
        
        Iterator it = januaryAmountList.iterator();
        while(it.hasNext()){
            String price = it.next().toString();
            monthTotal += (new Float(price).floatValue());
        }
        return monthTotal;
        
    }
 
        
}
